/**
 * 
 */
package com.roc.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.NonUniqueResultException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;

import com.roc.bean.MerinfoDetail;
import com.roc.dao.DaoException;
import com.roc.dao.IMerchantDao;
import com.roc.pojo.AreaCode;
import com.roc.pojo.Esmidinfo;
import com.roc.pojo.EsmidinfoMerinfo;
import com.roc.pojo.EsmidinfoTmp;
import com.roc.pojo.MerinfoChange;
import com.roc.pojo.Operators;
import com.roc.pojo.SettingBean;
import com.roc.pojo.TblEditMchtBankAccount;
import com.roc.pojo.TblEditMchtBizDeal;
import com.roc.pojo.TblEditMchtBizFee;
import com.roc.pojo.TblEditMchtInf;
import com.roc.pojo.TblEditTermInf;
import com.roc.pojo.TblEditTermRiskCfg;
import com.roc.util.Constance;
import com.roc.util.constant.MerchantStatus;

/**
 * @author RenPengFei 2013-5-11 下午4:51:07
 */
@Repository
public class MerchantDaoImpl extends BaseDaoImpl implements IMerchantDao {
	
	public Operators findUniqueMerchantByNo(String accountNo) throws DaoException {
		Operators result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from Operators m where m.accountNo = :accountNo and m.roleType <> '17' ");
			query.setString("accountNo", accountNo);
			result = (Operators) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询Operators不唯一", nure);
			throw new DaoException("查询代理商不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}

	public Operators loadMerchantById(Long id) throws DaoException {
		Operators result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			result = (Operators) session.get(Operators.class, id);
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	

	@SuppressWarnings("unchecked")
	@Override
	public List<Operators> listSomeMerchants(String merType, Long merchantId,String orgCode)
			throws DaoException {
		List<Operators> merchantList=new ArrayList<Operators>();
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb=new StringBuffer("from Operators m where 1=1 ");
			if (Constance.isNotEmpty(merType) ){
				if(Constance.MERCHANT_TYPE_SYSTEM.equals(merType)){
						sb.append(" and m.merType='").append(Constance.MERCHANT_TYPE_DAILI).append("'" );
				}
				if(Constance.MERCHANT_TYPE_DAILI.equals(merType)){
						sb.append(" and m.merType='").append(Constance.MERCHANT_TYPE_DAILI).append("'" );	
						//sb.append(" and ( m.id=").append(merchantId).append(" or m.superiorId=").append(merchantId).append(") ");
						sb.append(" and m.orgCode='"+orgCode+"'");
						sb.append(" or m.merType='").append(Constance.MERCHANT_TYPE_SYSTEM).append("'" );	
						
				}
				if(Constance.MERCHANT_TYPE_RECOMANFER.equals(merType)){
						sb.append(" and m.merType='").append(Constance.MERCHANT_TYPE_DAILI).append("'" );	
						sb.append(" and m.recommenderId=").append(merchantId);
				}
			}
			sb.append(" order by m.realName ");
			
			Query query=session.createQuery(sb.toString());
			merchantList=query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询代理商信息出错");
		}
		return merchantList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Operators> listRecommenders() throws DaoException {
		List<Operators> merchantList=new ArrayList<Operators>();
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb=new StringBuffer("from Operators m where m.merType=?  order by m.realName ");
			
			Query query=session.createQuery(sb.toString());
			query.setString(0, Constance.MERCHANT_TYPE_RECOMANFER);
			merchantList=query.list();
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询推荐人信息出错");
		}
		return merchantList;
	}
	@Override
	public long isDeleted(long merchantId) throws DaoException {
		long count=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("select count(*) from Operators m where m.superiorId =? ");
			query.setLong(0, merchantId);
	
			count = (Long) query.uniqueResult();
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return count;
	}
	@Override
	public  Esmidinfo queryMerinfobyid(String id) throws DaoException {
		Esmidinfo result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery(" from Esmidinfo m where m.mid = :id");
			query.setString("id", id);
			result = (Esmidinfo) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	
	
	//根据商户号码查询商户
	@Override
	public MerinfoDetail queryMerinfoByNo(String merchantNo) throws DaoException {
		MerinfoDetail md=null;
		Session session= sessionFactory.getCurrentSession();
		String  sql="SELECT o.org_name,ta.area_name,et.terNum,t.mcc_product_name,'' bankbranchname,        "  
					+" m.MID ,m.MER_NAME ,m.MER_BR_NAME,m.ENG_NAME,m.ENG_BR_NAME,m.ADDR,m.MER_KIND,m.TEL ,"  
				+" m.FAX  ,m.EMAIL,m.ZIPCODE ,m.CONTACT,m.INTER_CONTACT ,m.ACC_BANK ,   "  
				+" m.ACC_NO,m.COMM_RATE ,m.COMM_MODE,m.COMM_AMT ,m.BELONG_BRANCH   ,m.MEMO           "  
				+",PERSONAL_NAME,PERAONAL_PHONE ,CARD_TYPE ,CARD_NO ,BANK_LICENSE ,BUSINESS_LICENCES ,       "  
				+"TAX_REGISTRATION_CERTIFICATE ,ORGANIZATION_CODE  ,CARD_URL ,BANK_LICENSE_URL ,             "  
				+"BUSINESS_LICENCES_URL ,ORGANIZATION_CODE_URL ,TAX_REGIST_CERT_URL ,PLACE_OF_BUSINESS_URL ,PIC_URL1 ,   "  
				+"PIC_URL2,PIC_URL3 ,PIC_URL4 ,PIC_URL7 ,PIC_URL6 ,PIC_URL5,cycleid,m.tranList, et.amount1,et.DAY_TOTAL_AMT1, "
				+"et.MONTH_TOTAL_AMT1,et.AMOUNT2,et.DAY_TOTAL_AMT2,et.MONTH_TOTAL_AMT3,et.OPERATORS,m.mcc,PIC_URL_8,PIC_URL_9,PIC_URL_10,m.SETT_BANK_ID "
				+",m.flag,et.agent_id,m.acc_type "
				+" FROM ESMIDINFO_TMP m                                                                      "  
				+"LEFT JOIN esmidinfo_tmp_merinfo et                                                         "  
				+"ON m.mid=et.merchant                                                                       "  
				+"LEFT JOIN t_org  o                                                                         "  
				+"ON o.org_code=m.belong_branch                                                              "  
				+"LEFT JOIN  t_mcc_code  t                                                                   "  
				+"ON t.mcc_code=m.mcc                                                                        "  
				//+"LEFT JOIN paybankinfo p                                                                    "  
				//+"ON p.bankbranchid=m.sett_bank_id                                                           "  
				+"LEFT JOIN t_area_code ta                                                                   "  
				+"ON ta.area_code=et.area_code                                                                "  
				+"  where  m.mid=?" ;
		
		try {
			Query  query =session.createSQLQuery(sql);
			query.setString(0, merchantNo);
			Iterator iterator= query.list().iterator();
			while(iterator.hasNext()){
				md=new MerinfoDetail();
				Object[] obj = (Object[]) iterator.next();
				 for(int i=0;i<obj.length;i++){
					 if(obj[i]==null){
						 obj[i]="";
					 }
				 } 
				md.setOrgName(obj[0].toString());                 
				md.setAreaName(obj[1].toString());                 
				md.setTerNums (obj[2].toString());                 
				md.setMccName (obj[3].toString());   
				md.setBankName(obj[4].toString());                 
				md.setMid(obj[5].toString());                      
				md.setMerName (obj[6].toString());                 
				md.setMerBrName(obj[7].toString());                
				md.setEngName (obj[8].toString());                 
				md.setEngBrName (obj[9].toString());               
				md.setAddr(obj[10].toString());                     
				md.setMerKind(obj[11].toString());                  
				md.setTel (obj[12].toString());
				md.setFax (obj[13].toString());
				md.setEmail (obj[14].toString());
				md.setZipCode (obj[15].toString());
				md.setContact (obj[16].toString());
				md.setInterContact (obj[17].toString());
				md.setAccBank (obj[18].toString());                 
				md.setAccNo (obj[19].toString());                   
				md.setCommRate (obj[20].toString());                
				md.setCommMode(obj[21].toString());                
				md.setCommAmt(obj[22].toString());                  
				md.setBelongBranch (obj[23].toString());            
				md.setMeMo(obj[24].toString());                     
				md.setPerName(obj[25].toString());                  
				md.setPerPhone(obj[26].toString());                 
				md.setCardType(obj[27].toString());                 
				md.setCardNo(obj[28].toString());                   
				md.setBankLicense(obj[29].toString());              
				md.setBusLicense (obj[30].toString());              
				md.setTaxRegCer (obj[31].toString());               
				md.setOrganizationCode(obj[32].toString());         
				md.setCardUrl (obj[33].toString());                 
				md.setBankLicenseUrl(obj[34].toString());           
				md.setBusinessLUrl (obj[35].toString());           
				md.setOrganizationCodeUrl(obj[36].toString());      
				md.setTaxRegCerUrl (obj[37].toString());            
				md.setPlaceOfBusUrl(obj[38].toString());           
				md.setPicUrl1(obj[39].toString());                  
				md.setPicUrl2 (obj[40].toString());                 
				md.setPicUrl3(obj[41].toString());                  
				md.setPicUrl4 (obj[42].toString());                 
				md.setPicUrl5  (obj[45].toString());                
				md.setPicUrl6 (obj[44].toString());                 
				md.setPicUrl7 (obj[43].toString());                 
				md.setCycleId(obj[46].toString());
				md.setTranTypes(obj[47].toString());
				md.setAmount1 (obj[48].toString());  
				md.setDayAmount1 (obj[49].toString());  
				md.setMouthAmount1 (obj[50].toString());  
				md.setAmount2 (obj[51].toString());  
				md.setDayAmount2 (obj[52].toString());  
				md.setMouthAmount2 (obj[53].toString());  
				md.setOpertors(obj[54].toString());
				md.setMcc(obj[55].toString());
				md.setPicUrl8(obj[56].toString());
				md.setPicUrl9(obj[57].toString());
				md.setPicUrl10(obj[58].toString());
				md.setSettBankId(obj[59].toString());
				md.setFlag(obj[60].toString());
				md.setAgentId(obj[61].toString());
				md.setAcctType(obj[62].toString());
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("查询商户出错！");
		}
		
		return md;
	}
	//同步商户关联表
	@Override
	public void theSameMerTable(String mid,String  tmpMid ) throws DaoException {
		
		try {
			Session session = sessionFactory.getCurrentSession();
			String  sql="insert into esmidinfo_merinfo ("
					   +" select '"+mid+"' as MERCHANT,  AMOUNT1, AMOUNT2,"
					   +"DAY_TOTAL_AMT1,DAY_TOTAL_AMT2, "
					   +"MONTH_TOTAL_AMT1,MONTH_TOTAL_AMT3,"
					   +" CYCLEID,AGENT_ID,PERSONAL_NAME,PERAONAL_PHONE,CARD_TYPE,CARD_NO,BANK_LICENSE,BUSINESS_LICENCES,"
					   +" TAX_REGISTRATION_CERTIFICATE ,ORGANIZATION_CODE,CARD_URL,BANK_LICENSE_URL,BUSINESS_LICENCES_URL,"
					   +" ORGANIZATION_CODE_URL,TAX_REGIST_CERT_URL,PLACE_OF_BUSINESS_URL,PIC_URL1,PIC_URL2,PIC_URL3,PIC_URL4,"
					   +" OPERATORS,DT_CREATE,DT_LASTMOD,PIC_URL7,PIC_URL6,PIC_URL5,AREA_CODE,TERNUM,PIC_URL_8,PIC_URL_9,PIC_URL_10 "
					   +" from esmidinfo_tmp_merinfo where merchant='"+tmpMid+"')";
			
			Query query=session.createSQLQuery(sql);
			query.executeUpdate();
			
		}catch (Exception e) {
			logger.debug("同步表失败", e);
			throw new DaoException("同步表失败");
		}
		
	}
	//同步商户表
	@Override
	public void theSameEsTable(String mid,String tmpMid) throws DaoException {
		try {
			Session session = sessionFactory.getCurrentSession();
			
			String  sql="insert into ESMIDINFO ( "
					+" SELECT '"+mid+"' MID,MER_NAME,MER_BR_NAME,ENG_NAME,ENG_BR_NAME,MCC,ADDR,MER_KIND,TEL,FAX,EMAIL,"
					+" ZIPCODE,CONTACT,INTER_CONTACT ,SETT_BANK_ID,ACC_BANK,ACC_NO,FC_CARD,MIS_FLAG,"
					+" COMM_RATE,COMM_MODE,COMM_AMT,COMM_DIV_NUM,DIV_LWM1,DIV_COMM1,DIV_LWM2,DIV_COMM2,"
					+" DIV_LWM3,DIV_COMM3,DIV_LWM4,DIV_COMM4,BELONG_BRANCH ,MEMO,RSV1,RSV2,RSV3,RSV4,"
					+String.valueOf(MerchantStatus.S_1.getCode())+" as FLAG,JOIN_DATE,MODI_DATE,TELLER,ROUTEFLAG,ROUTEID,PREPROFLAG,SETFLAG,TOTALTRANS,"
					+" TOTALAMT,CURTOTALTRANS ,CURTOTALAMT,'"+Constance.currTime()+"',CYCLETIME,TRANLIST,TRANS_TIMES,TRANS_TOTAL_AMT_DJ,TRANS_TOTAL_AMT_JJ,merber_fee_type,merber_fee,merber_fee_amt,merber_flag,MER_STATUS,acc_type"
					+"  FROM esmidinfo_tmp WHERE mid='"+tmpMid+"')";
			
			Query query=session.createSQLQuery(sql);
			query.executeUpdate();
			
		}catch (Exception e) {
			logger.debug("同步表失败", e);
			throw new DaoException("同步表失败");
		}
		
	}

	@Override
	public EsmidinfoTmp queryMerchant(String mid) throws DaoException {
		EsmidinfoTmp esmidinfoTmp=null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query=session.createQuery("from EsmidinfoTmp t where t.mid=?");
			query.setParameter(0, mid);
			esmidinfoTmp=(EsmidinfoTmp)query.uniqueResult();
			
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询商户【“+mid+”】的基本信息出错");
		}
		return esmidinfoTmp;
	}

	@Override
	public MerinfoChange queryMerinfoChangeByNo(Long id) throws DaoException {
		// TODO Auto-generated method stub
		MerinfoChange merinfo=null;
		try{
		        StringBuffer sb = new StringBuffer();
		        sb.append("SELECT T . ID, T .MID, T .NAME_BUSI, T .CONTACT_PHONE_NO, T .BANK_ACCOUNT_NAME, T .OPEN_BANK, T .BANK_ACCOUNT, T .BANK_CODE, T .DEAL_CODE, ");
		        sb.append("T .REMARK, T .OPERATORS, T .DT_CREATE_TIME, T .DT_AUDITOR_TIME, T .URL1, T .URL2, T .URL3, T .URL4, T .AGENT_ID, T .ORG_CODE, T .STATUS, T .ACCOUNT_NO ");
		        sb.append("FROM APSMGM.T_MERINFO_CHANGE T");
			String sql="SELECT mc.mid,mc.mer_name,mc.mer_br_name,"
					+"mc.inter_contact,mc.PERAONAL_PHONE,mc.personal_name,"
					+"mc.card_type,mc.card_no,mc.addr,et.BELONG_BRANCH,em.AGENT_ID,mc.DEAL_CODE,mc.COMM_RATE,mc.COMM_MODE,mc.COMM_AMT,mc.OPERATORS,mc.url1,mc.url2,mc.url3,mc.url4,mc.id,"
					+"mc.ACC_BANK,mc.ACC_NO,mc.CONTACT,mc.amount1,mc.amount2,mc.DAY_TOTAL_AMT1,mc.DAY_TOTAL_AMT2,mc.MONTH_TOTAL_AMT1,mc.MONTH_TOTAL_AMT3,"
					+"mc.ENG_BR_NAME,mc.ENG_NAME,mc.tel,mc.acct_type "
					+"FROM t_merinfo_change mc,esmidinfo_tmp_merinfo em ,esmidinfo_tmp et  "
					+"WHERE mc.mid=em.merchant and mc.mid=et.mid and mc.id="+id;
			Session session=sessionFactory.getCurrentSession();
			Query query=session.createSQLQuery(sql);
			Iterator iter=query.list().iterator();
			while(iter.hasNext()){
				merinfo=new MerinfoChange();
				Object[] obj = (Object[]) iter.next();
				 for(int i=0;i<obj.length;i++){
					 if(obj[i]==null){
						 obj[i]="";
					 }
				 }
				merinfo.setMid(Constance.Null2EmptyTrim(obj[0]));
//				merinfo.setMerName(Constance.Null2EmptyTrim(obj[1]));
//				merinfo.setMerBrName(Constance.Null2EmptyTrim(obj[2]));
//				merinfo.setInterContact(Constance.Null2EmptyTrim(obj[3]));
//				merinfo.setPerPhone(Constance.Null2EmptyTrim(obj[4]));
//				merinfo.setPerName(Constance.Null2EmptyTrim(obj[5]));	
//				merinfo.setCardType(Constance.Null2EmptyTrim(obj[6]));
//				merinfo.setCardNo(Constance.Null2EmptyTrim(obj[7]));
//				merinfo.setAddr(Constance.Null2EmptyTrim(obj[8]));
					
				merinfo.setAgentId(Constance.Null2EmptyTrim(obj[10]));
         
				merinfo.setOrgCode(Constance.Null2EmptyTrim(obj[9]));
				merinfo.setDealCode(Constance.Null2EmptyTrim(obj[11]));
				
//				if(!"".equals(Constance.Null2EmptyTrim(obj[12]))){
//					merinfo.setCommRate(Double.parseDouble(Constance.Null2EmptyTrim(obj[12])));
//				}
//						
//				merinfo.setCommMode(Constance.Null2EmptyTrim(obj[13]));
//				
//				if(!"".equals(Constance.Null2EmptyTrim(obj[14]))){
//					merinfo.setCommAmt(Double.parseDouble(Constance.Null2EmptyTrim(obj[14])));
//				}
				
				merinfo.setOperators(Constance.Null2EmptyTrim(obj[15]));
				merinfo.setUrl1(Constance.Null2EmptyTrim(obj[16]));
				merinfo.setUrl2(Constance.Null2EmptyTrim(obj[17]));
				merinfo.setUrl3(Constance.Null2EmptyTrim(obj[18]));
				merinfo.setUrl4(Constance.Null2EmptyTrim(obj[19]));
//				merinfo.setId(Long.parseLong(Constance.Null2EmptyTrim(obj[20])));
//				merinfo.setAccBank(Constance.Null2EmptyTrim(obj[21]));
//				merinfo.setAccNo(Constance.Null2EmptyTrim(obj[22]));
//				merinfo.setContact(Constance.Null2EmptyTrim(obj[23]));
//				if(!"".equals(Constance.Null2EmptyTrim(obj[24]))){
//					merinfo.setAmount1(Double.parseDouble(Constance.Null2EmptyTrim(obj[24])));
//				}
//				if(!"".equals(Constance.Null2EmptyTrim(obj[25]))){
//					merinfo.setAmount2(Double.parseDouble(Constance.Null2EmptyTrim(obj[25])));
//				}
//				if(!"".equals(Constance.Null2EmptyTrim(obj[26]))){
//					merinfo.setDayAmount1(Double.parseDouble(Constance.Null2EmptyTrim(obj[26])));
//				}
//				if(!"".equals(Constance.Null2EmptyTrim(obj[27]))){
//					merinfo.setDayAmount2(Double.parseDouble(Constance.Null2EmptyTrim(obj[27])));
//				}
//				if(!"".equals(Constance.Null2EmptyTrim(obj[28]))){
//					merinfo.setMouthAmount1(Double.parseDouble(Constance.Null2EmptyTrim(obj[28])));
//				}
//				if(!"".equals(Constance.Null2EmptyTrim(obj[29]))){
//					merinfo.setMouthAmount2(Double.parseDouble(Constance.Null2EmptyTrim(obj[29])));
//				}
//				merinfo.setEngBrName(Constance.Null2EmptyTrim(obj[30]));
//				merinfo.setEngName(Constance.Null2EmptyTrim(obj[31]));
//				merinfo.setTel(Constance.Null2EmptyTrim(obj[32]));
//				merinfo.setAcctType(Constance.Null2EmptyTrim(obj[33]));
			}
		}catch(Exception e){
			logger.debug(e.getMessage(),e);
			throw new DaoException("查询变更商户信息出错");
		}
		return merinfo;
	}

	
	/**
	 * 查询身份证的使用次数
	 * @param cardNo
	 * @return
	 * @throws DaoException
	 */
	public int checkCardNoTimes(String cardNo) throws DaoException {
		int i = 0;
		try {
			String sql = "SELECT count(*) count from ESMIDINFO_tmp t1,ESMIDINFO_tmp_merinfo t2 where t1.MID = t2.MERCHANT and  t2.CARD_NO = '"+cardNo+"' and t1.flag not in('14','15') ";
			Session session=sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			String str= Constance.Null2EmptyTrim(query.uniqueResult());
			if(!"".equals(str)){
				i = Integer.parseInt(str);
			}
			
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询身份证使用次数出错,证件号:"+cardNo);
		}
		return i;
	}

	/**
	 * 查询营业执照的使用次数
	 * @param busLicences
	 * @return
	 * @throws DaoException
	 */
	public int checkBusLicencesTimes(String busLicences) throws DaoException {
		int i = 0;
		try {
			String sql = " SELECT count(*) count from ESMIDINFO_tmp_merinfo where BUSINESS_LICENCES = '"+busLicences+"' ";
			Session session=sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			String str= Constance.Null2EmptyTrim(query.uniqueResult());
			if(!"".equals(str)){
				i = Integer.parseInt(str);
			}
			
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询营业执照的使用次数失败, 执照号:"+busLicences);
		}
		return i;
	}

	@Override
	public boolean queryMerinfoChangeByMid(String mid, String flag) throws DaoException {
		// TODO Auto-generated method stub
		int i=0;
		try{
		        String hql;
		        if(flag.equals("Z")){
		           hql="select count(*) from MerinfoChange m where m.mid='"+mid+"' and m.status = 'Z'";
		           Session session=sessionFactory.getCurrentSession();
	                   Query query = session.createQuery(hql);
	                   String str= Constance.Null2EmptyTrim(query.uniqueResult());
	                        if(!"".equals(str)){
	                                i = Integer.parseInt(str);
	                        }
		        }else if (flag.equals("W")) {
		           hql="select count(*) from MerinfoChange m where m.mid='"+mid+"' and m.status = 'W'";
		           Session session=sessionFactory.getCurrentSession();
	                   Query query = session.createQuery(hql);
	                   String str= Constance.Null2EmptyTrim(query.uniqueResult());
	                   if(!"".equals(str)){
	                       i = Integer.parseInt(str);
	                   }
	                   
                        }
		        
			
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询商户审核表失败,商户号:"+mid);
		}
		if(i>0){
			return true;
		}
		return false;
	}

	@Override
	public String handleMerchantno(String one, String two, String three) throws DaoException  {
		Connection con=null;
		CallableStatement query =null;
		try {
			  con=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			 query = con.prepareCall("{ call get_mcht_cd_sn(?,?,?,?) }"); 
 
			query.setString(1, one);
			query.setString(2, two);
			query.setString(3, three);
			query.registerOutParameter(4, java.sql.Types.VARCHAR);
			query.execute();
			String result=query.getString(4);
			return result;
		}catch (Exception e) {
			logger.error("Dao查询出错",e);
			throw new DaoException("查询出错",e);
		}finally {
			try {
				if(query!=null){
					query.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
 
	}
	
	@Override
	public long addMerinfoAgentRelations(String merinfoNo, String merinfoName,Long agentId) throws DaoException {
		Connection con=null;
		CallableStatement query =null;
		long result=0;
		try {
			con=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			query = con.prepareCall("{ call add_merinfo_agent_relation(?,?,?,?) }"); 
			query.setString(1, merinfoNo);
			query.setString(2, merinfoName);
			query.setLong(3, agentId);
			query.registerOutParameter(4, java.sql.Types.NUMERIC);
			query.execute();
			 result= query.getLong(4);
			return result;
		}catch (Exception e) {
			logger.error("添加商户代理商关系出错",e);
			throw new DaoException("添加商户代理商关系出错",e);
		}finally {
			try {
				if(query!=null){
					query.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	@Override
	public void addMerinfoModule(String opAccount, String currentOpAccount) throws DaoException {
		Connection con=null;
		CallableStatement query =null;
		 
		try {
			con=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			query = con.prepareCall("{ call ADD_MERINFO_OPERATOR_INFO(?,?) }"); 
			query.setString(1, opAccount);
			query.setString(2, currentOpAccount);
			//query.registerOutParameter(3, java.sql.Types.NUMERIC);
			query.execute();
			/* String result= query.getString(3 );
			return result;*/
		}catch (Exception e) {
			logger.error("添加商户代理商关系出错",e);
			throw new DaoException("添加商户代理商关系出错",e);
		}finally {
			try {
				if(query!=null){
					query.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	 

	@Override
	public List queryBySqlToVo(String SQL, Object... parmas)
			throws DaoException {
		// TODO Auto-generated method stub
		
		Query q=sessionFactory.getCurrentSession().createSQLQuery(SQL);
		if(parmas!=null&&parmas.length>0){
			for(int i=0;i<parmas.length;i++){
				q.setParameter(i, parmas[i]);
			}
		}
		return q.list();
	}
    /**
     * 商户批量操作调用pkg
     */
	@Override
	public List<String> updateMerStatusBanch(String merInfo, String type,
			String account) throws DaoException {
		Connection con=null;
		CallableStatement query =null;
		List<String> list = new ArrayList<String>();
		try {
			con=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
			query = con.prepareCall("{Call pkg_batch_mer_main.pmerBatchOperation(?,?,?,?,?)}");
			query.setString(1, merInfo);
			query.setString(2, type);
			query.setString(3, account);
			query.registerOutParameter(4, java.sql.Types.VARCHAR);
			query.registerOutParameter(5, java.sql.Types.VARCHAR);
			query.executeUpdate();
			String code = query.getString(4);
			String desc = query.getString(5);
			list.add(code);
			list.add(desc);
		}catch(Exception e){
			logger.error("修改商户状态错误",e);
			throw new DaoException("修改商户状态错误",e);
		}finally{
			try{
				query.close();
				con.close();
			}catch(Exception e){
				logger.error("关闭数据库连接错误",e);
				throw new DaoException("关闭数据库连接错误",e);
			}
		}
		return list;
	}

	@Override
	public Operators queryOperByRoleAndOrg(String roleType, String orgCode)
			throws DaoException {
		Operators result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from Operators m where m.roleType = :roleType and m.orgCode = :orgCode");
			query.setString("roleType", roleType);
			query.setString("orgCode", orgCode);
			result = (Operators) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询机构管理员不唯一", nure);
			throw new DaoException("查询机构管理员不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}

	

	
	
	 
}
